import pymysql
from config import Config
import logging
from flask import Flask, jsonify, request

# 配置日志
logging.basicConfig(level=logging.INFO)
app = Flask(__name__)

# 优化后的传感器数据查询 - 使用窗口函数
sql_sensor_data = """
    SELECT 
        sensor_id, 
        location,  
        displacement, 
        yuanshishuju,
        temperature,  
        timestamp
    FROM (
        SELECT 
            f.sensor_id, 
            s.model AS location,  
            f.filtered_value AS displacement, 
            r.raw_value AS yuanshishuju,
            r.temp AS temperature,  
            f.receive_time AS timestamp,
            ROW_NUMBER() OVER (PARTITION BY f.sensor_id ORDER BY f.receive_time DESC) as rn
        FROM filtered_data f
        JOIN raw_data r ON f.raw_data_id = r.id
        JOIN sensor_info s ON f.sensor_id = s.sensor_id
    ) t
    WHERE rn = 1
"""

# 优化后的预警数据查询 - 使用窗口函数
sql_alert_data = """
    SELECT 
        sensor_id, 
        location, 
        alert_level, 
        alert_value, 
        threshold_value, 
        timestamp
    FROM (
        SELECT 
            a.sensor_id, 
            s.model AS location, 
            a.alert_level, 
            a.alert_value, 
            a.threshold_value, 
            a.timestamp,
            ROW_NUMBER() OVER (PARTITION BY a.sensor_id ORDER BY a.timestamp DESC) as rn
        FROM alert_records a
        JOIN sensor_info s ON a.sensor_id = s.sensor_id
    ) t
    WHERE rn = 1
"""


def get_db_connection():
    try:
        conn = pymysql.connect(
            host=Config.MYSQL_HOST,
            user=Config.MYSQL_USER,
            password=Config.MYSQL_PASSWORD,
            db=Config.MYSQL_DB,
            charset=Config.MYSQL_CHARSET,
            cursorclass=pymysql.cursors.DictCursor,
            connect_timeout=5
        )
        logging.info("✅ 数据库连接成功")
        return conn
    except pymysql.err.OperationalError as e:
        logging.error(f"❌ 数据库连接失败（操作错误）: {e}")
        raise
    except Exception as e:
        logging.error(f"❌ 未知数据库错误: {e}")
        raise


@app.route('/api/data/latest', methods=['GET'])
def get_latest_sensor_data():
    conn = None
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 添加查询超时设置
            cursor.execute("SET SESSION MAX_EXECUTION_TIME=10000")  # 10秒超时

            # 执行优化后的SQL查询
            logging.info("开始执行传感器数据查询...")
            cursor.execute(sql_sensor_data)
            sensor_data = cursor.fetchall()
            logging.info(f"传感器数据查询完成，返回 {len(sensor_data)} 条记录")

            logging.info("开始执行预警数据查询...")
            cursor.execute(sql_alert_data)
            alert_data = cursor.fetchall()
            logging.info(f"预警数据查询完成，返回 {len(alert_data)} 条记录")

            # 判断是否有预警数据
            if not alert_data:
                # 如果没有预警信息，则为所有传感器设置 "正常"
                for sensor in sensor_data:
                    sensor['alert'] = "正常"
            else:
                # 如果有预警信息，逐个匹配
                for sensor in sensor_data:
                    matched_alert = next((alert for alert in alert_data if alert['sensor_id'] == sensor['sensor_id']),
                                         None)
                    if matched_alert:
                        sensor['alert'] = matched_alert.get('alert_level', "正常")
                    else:
                        sensor['alert'] = "正常"

        # 传感器ID
        target_sensor_ids = {'267456', '017749'}  #
        filtered_data = [sensor for sensor in sensor_data if sensor['sensor_id'] in target_sensor_ids]
        logging.info(f"筛选完成，返回 {len(filtered_data)} 条记录")

        return jsonify({"status": "success", "data": filtered_data})
    except (pymysql.err.OperationalError, pymysql.MySQLError) as e:
        logging.error(f"❌ 数据库错误: {e}")
        return jsonify({"status": "error", "message": str(e)}), 500
    except Exception as e:
        logging.error(f"❌ 未知错误: {e}")
        return jsonify({"status": "error", "message": str(e)}), 500
    finally:
        if conn:
            conn.close()


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)